package controller

import (
	"digital/server/common"
	"digital/server/model"
	"digital/server/response"
	"fmt"
	mssql "github.com/denisenkom/go-mssqldb"
	"github.com/gin-gonic/gin"
	"github.com/jinzhu/gorm"
	"log"
	"math"
	"net/http"
	"strconv"
	"time"
)

// 发布年检的函数
func ReleaseAS(c *gin.Context) {
	uid := c.PostForm("uid")
	publisher, _ := strconv.Atoi(uid)
	Time := c.PostForm("applyTime")
	ApplyTime, _ := time.Parse("2006-01-02 15:04:05", Time) // 转换时间的格式
	year := c.PostForm("year")
	Year, _ := strconv.Atoi(year)
	AsName := c.PostForm("AsName")

	fileID := UploadFileToCOS(c) // 上传文件并获取文件表中的主键

	var state = -1 // state为1则表示发布年检成功

	// 如果上传文件失败，则直接返回错误
	if fileID == -1 {
		state = 0
		response.Response(c, http.StatusBadRequest, 400, gin.H{"state": state}, "上传文件发生错误，请重试")
		return
	}

	// 开始事务
	tx := common.GetDB().Begin()

	defer func() {
		if r := recover(); r != nil {
			// 发生错误时回滚事务
			tx.Rollback()
			response.Response(c, http.StatusInternalServerError, 500, nil, "数据库操作失败，请重试")
		}
	}()

	// 插入年检信息
	IssueInfo := model.IssueAS{
		CPName:    AsName,
		ATID:      fileID,
		Publisher: publisher,
		PTime:     ApplyTime,
		Year:      Year,
		State:     1,
	}

	if err := tx.Table("issue_as").Create(&IssueInfo).Error; err != nil {
		// 插入失败时回滚事务
		tx.Rollback()
		fmt.Println("执行 SQL 语句时发生错误:", err)
		response.Response(c, http.StatusBadRequest, 400, nil, "发生错误，请重试")
		return
	}

	// 查询所有 state=1 的社团信息
	var clubs []model.Club
	if err := tx.Table("clubs").Select("id, Cname, LeaderID").Where("State = ?", 1).Find(&clubs).Error; err != nil {
		// 查询失败时回滚事务
		tx.Rollback()
		fmt.Println("查询 clubs 表时发生错误:", err)
		response.Response(c, http.StatusInternalServerError, 500, nil, "查询社团信息失败")
		return
	}

	// 遍历每个社团，检查 msg_sender 表中是否存在 type=7 且 sid=社团id 的记录
	for _, club := range clubs {
		var msgSender model.MsgSender
		if err := tx.Table("msg_sender").Where("type = ? AND sid = ?", 7, club.Cid).First(&msgSender).Error; err != nil {
			if gorm.ErrRecordNotFound == err {
				// 不存在记录，插入新的记录
				newMsgSender := model.MsgSender{
					Type:  7,
					SID:   club.Cid,
					SName: club.Cname,
				}
				if err := tx.Table("msg_sender").Create(&newMsgSender).Error; err != nil {
					// 插入失败时回滚事务
					tx.Rollback()
					fmt.Println("插入 msg_sender 表时发生错误:", err)
					response.Response(c, http.StatusInternalServerError, 500, nil, "插入消息发送者失败")
					return
				}
				msgSender = newMsgSender
			} else {
				// 查询失败时回滚事务
				tx.Rollback()
				fmt.Println("查询 msg_sender 表时发生错误:", err)
				response.Response(c, http.StatusInternalServerError, 500, nil, "查询消息发送者失败")
				return
			}
		}

		// 更新消息表 msg
		newMsg := model.Message{
			Sid:      msgSender.ID,
			Rid:      club.LeaderID,
			Lid:      15,
			Approver: publisher,
			Mtime:    ApplyTime,
		}

		if err := tx.Table("msg").Create(&newMsg).Error; err != nil {
			// 插入失败时回滚事务
			tx.Rollback()
			fmt.Println("插入 msg 表时发生错误:", err)
			response.Response(c, http.StatusInternalServerError, 500, nil, "插入消息失败")
			return
		}
	}

	// 提交事务
	tx.Commit()
	state = 1

	DB := common.GetDB()
	var ass []model.IssueAS
	err2 := DB.Table("issue_as").Where("State=?", 1).Find(&ass).Error
	if err2 != nil {
		response.Response(c, http.StatusBadRequest, 400, nil, "发生错误，请重试")
		log.Println("Error querying user:", err2)
		return
	}

	var as_clubInfo [][]model.Club //每年年检没完成的社团
	var as_ratio []float64
	var as_Names []string
	var as_files []model.AllFile

	for _, as := range ass {
		//获得所有评优的发布人姓名
		var user model.User
		err := DB.Table("users").Where("uid = ?", as.Publisher).Find(&user).Error
		if err != nil {
			log.Println("Error querying user:", err)
			return
		}
		as_Names = append(as_Names, user.Uname)

		//获得所有评优的文件
		var allfile model.AllFile
		err = DB.Table("all_file").Where("id = ?", as.ATID).Find(&allfile).Error
		if err != nil {
			log.Println("Error querying file:", err)
			return
		}
		as_files = append(as_files, allfile)

		//获得所有的年检记录
		var asApply []model.Apply_AS
		err2 := DB.Table("apply_as").Where("CPid = ? and state=?", as.CPID, 1).Find(&asApply).Error
		if err2 != nil {
			log.Println("Error querying apply_ae:", err2)
			return
		}

		//获得年检申请审批通过的社团id
		var as_applyId []int
		for _, apply := range asApply {
			as_applyId = append(as_applyId, apply.CID)
		}

		//获得年检年份所有社团的id
		year := as.Year
		var as_clubs []model.Club
		var as_totalID []int

		err = DB.Table("clubs").Where("YEAR(Edate) <= ? AND (YEAR(Odate) >= ? OR Odate IS NULL) and exist!=?", year, year, 0).Find(&as_clubs).Error
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"code": 400, "message": "查询社团数据失败"})
			return
		}
		for _, club := range as_clubs {
			as_totalID = append(as_totalID, club.Cid)
		}

		//获取年检完成度
		var ratio float64
		if len(as_totalID) == 0 {
			ratio = 0
		} else {
			ratio = float64(len(as_applyId)) / float64(len(as_totalID))
		}
		as_ratio = append(as_ratio, math.Round(ratio*100)/100)

		//获得没有申请年检的社团id
		var as_diff []int
		applyMap := make(map[int]bool)
		for _, id := range as_applyId {
			applyMap[id] = true
		}
		for _, id := range as_totalID { // 遍历totalID，如果元素不在applyMap中，则添加到diff中
			if _, exists := applyMap[id]; !exists {
				as_diff = append(as_diff, id)
			}
		}

		//获取未完成的社团信息
		var n_clubs []model.Club
		for _, id := range as_diff {
			var club model.Club
			err := DB.Table("clubs").Where("id = ?", id).Find(&club).Error
			if err != nil {
				log.Println("Error querying club:", err)
				continue
			}
			n_clubs = append(n_clubs, club)
		}
		as_clubInfo = append(as_clubInfo, n_clubs)
	}
	response.Response(c, http.StatusOK, 200, gin.H{"ass": ass, "as_ratio": as_ratio, "as_Names": as_Names, "as_files": as_files}, "查询成功")
	// 返回成功响应
	//response.Success(c, gin.H{"state": state}, "发布年检信息成功！")
}

// 发布评优的函数
func ReleaseCE(c *gin.Context) {
	uid := c.PostForm("uid")
	publisher, _ := strconv.Atoi(uid)
	Time := c.PostForm("applyTime")
	ApplyTime, _ := time.Parse("2006-01-02 15:04:05", Time) // 转换时间的格式
	year := c.PostForm("year")
	Year, _ := strconv.Atoi(year)
	CeName := c.PostForm("CeName")

	fileID := UploadFileToCOS(c) // 上传文件并获取文件表中的主键
	fmt.Println(fileID)
	var state = -1 // state为1则表示发布评优成功

	// 如果上传文件失败，则直接返回错误
	if fileID == -1 {
		state = 0
		response.Response(c, http.StatusBadRequest, 400, gin.H{"state": state}, "上传文件发生错误，请重试")
		return
	}

	// 开始事务
	tx := common.GetDB().Begin()

	defer func() {
		if r := recover(); r != nil {
			// 发生错误时回滚事务
			tx.Rollback()
			response.Response(c, http.StatusInternalServerError, 500, nil, "数据库操作失败，请重试")
		}
	}()

	// 插入评优信息
	CeInfo := model.IssueCE{
		CEName:    CeName,
		ATID:      fileID,
		Publisher: publisher,
		PTime:     ApplyTime,
		Year:      Year,
		State:     1,
	}

	if err := tx.Table("issue_ce").Create(&CeInfo).Error; err != nil {
		// 插入失败时回滚事务
		tx.Rollback()
		fmt.Println("执行 SQL 语句时发生错误:", err)
		response.Response(c, http.StatusBadRequest, 400, nil, "发生错误，请重试")
		return
	}

	// 查询所有 state=1 的社团信息
	var clubs []model.Club
	if err := tx.Table("clubs").Select("id, Cname, LeaderID").Where("State = ?", 1).Find(&clubs).Error; err != nil {
		// 查询失败时回滚事务
		tx.Rollback()
		fmt.Println("查询 clubs 表时发生错误:", err)
		response.Response(c, http.StatusInternalServerError, 500, nil, "查询社团信息失败")
		return
	}

	// 遍历每个社团，检查 msg_sender 表中是否存在 type=8 且 sid=社团id 的记录
	for _, club := range clubs {
		var msgSender model.MsgSender
		if err := tx.Table("msg_sender").Where("type = ? AND sid = ?", 8, club.Cid).First(&msgSender).Error; err != nil {
			if gorm.ErrRecordNotFound == err {
				// 不存在记录，插入新的记录
				newMsgSender := model.MsgSender{
					Type:  8,
					SID:   club.Cid,
					SName: club.Cname,
				}
				if err := tx.Table("msg_sender").Create(&newMsgSender).Error; err != nil {
					// 插入失败时回滚事务
					tx.Rollback()
					fmt.Println("插入 msg_sender 表时发生错误:", err)
					response.Response(c, http.StatusInternalServerError, 500, nil, "插入消息发送者失败")
					return
				}
				msgSender = newMsgSender
			} else {
				// 查询失败时回滚事务
				tx.Rollback()
				fmt.Println("查询 msg_sender 表时发生错误:", err)
				response.Response(c, http.StatusInternalServerError, 500, nil, "查询消息发送者失败")
				return
			}
		}

		// 更新消息表 msg
		newMsg := model.Message{
			Sid:      msgSender.ID,
			Rid:      club.LeaderID,
			Lid:      16,
			Approver: publisher,
			Mtime:    ApplyTime,
		}
		if err := tx.Table("msg").Create(&newMsg).Error; err != nil {
			// 插入失败时回滚事务
			tx.Rollback()
			fmt.Println("插入 msg 表时发生错误:", err)
			response.Response(c, http.StatusInternalServerError, 500, nil, "插入消息失败")
			return
		}
	}

	// 提交事务
	tx.Commit()
	state = 1

	DB := common.GetDB()
	var ces []model.IssueCE
	err := DB.Table("issue_ce").Where("State=?", 1).Find(&ces).Error
	if err != nil {
		response.Response(c, http.StatusBadRequest, 400, nil, "发生错误，请重试")
		log.Println("Error querying user:", err)
		return
	}

	var ce_clubInfo [][]model.Club //每年评优没完成的社团
	var ce_ratio []float64
	var ce_Names []string
	var ce_files []model.AllFile
	for _, ce := range ces {
		//获得所有评优的发布人姓名
		var user model.User
		err := DB.Table("users").Where("uid = ?", ce.Publisher).Find(&user).Error
		if err != nil {
			log.Println("Error querying user:", err)
			return
		}
		ce_Names = append(ce_Names, user.Uname)

		//获得所有评优的文件
		var allfile model.AllFile
		err = DB.Table("all_file").Where("id = ?", ce.ATID).Find(&allfile).Error
		if err != nil {
			log.Println("Error querying file:", err)
			return
		}
		ce_files = append(ce_files, allfile)

		//获得所有的评优记录
		var ceApply []model.Apply_CE
		err1 := DB.Table("apply_ce").Where("CEid = ? and state=?", ce.CEID, 1).Find(&ceApply).Error
		if err1 != nil {
			log.Println("Error querying apply_ce:", err1)
			return
		}

		//获得评优申请审批通过的社团id
		var applyId []int
		for _, apply := range ceApply {
			applyId = append(applyId, apply.CID)
		}

		//获得评优年份所有社团的id
		year := ce.Year
		var clubs []model.Club
		var totalID []int
		err = DB.Table("clubs").Where("YEAR(Edate) <= ? AND (YEAR(Odate) >= ? OR Odate IS NULL) and exist!=?", year, year, 0).Find(&clubs).Error
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"code": 400, "message": "查询社团数据失败"})
			return
		}
		for _, club := range clubs {
			totalID = append(totalID, club.Cid)
		}

		//获取评优完成度
		var ratio float64
		if len(totalID) == 0 {
			ratio = 0
		} else {
			ratio = float64(len(applyId)) / float64(len(totalID))
		}
		ce_ratio = append(ce_ratio, math.Round(ratio*100)/100)

		//获得没有申请评优的社团id
		var diff []int
		applyMap := make(map[int]bool)
		for _, id := range applyId {
			applyMap[id] = true
		}
		for _, id := range totalID { // 遍历totalID，如果元素不在applyMap中，则添加到diff中
			if _, exists := applyMap[id]; !exists {
				diff = append(diff, id)
			}
		}

		//获取未完成的社团信息
		var n_clubs []model.Club
		for _, id := range diff {
			var club model.Club
			err := DB.Table("clubs").Where("id = ?", id).Find(&club).Error
			if err != nil {
				log.Println("Error querying club:", err)
				continue
			}
			n_clubs = append(n_clubs, club)
		}
		ce_clubInfo = append(ce_clubInfo, n_clubs)
	}
	response.Response(c, http.StatusOK, 200, gin.H{"ces": ces, "ce_clubInfo": ce_clubInfo, "ce_ratio": ce_ratio, "ce_Names": ce_Names, "ce_files": ce_files}, "查询成功")
}

// 社团负责人端，获取年检和评优的信息(包括与参与和已参与）
func GetManageASCE(c *gin.Context) {
	//先看是哪个社团的负责人，再查找当前已经发布过的年检或者评优的ID，再找apply表中是否有自己的信息
	userid := c.Query("uid")
	uid, _ := strconv.Atoi(userid)

	DB := common.GetDB()
	var clubInfo []model.UserClub
	err := DB.Table("user_clubs").Where("UID = ? AND Permission = ?", uid, 1).Find(&clubInfo).Error
	if err != nil {
		response.Response(c, http.StatusBadRequest, 400, nil, "查询社团用户表失败，请重试")
		log.Println("Error querying user:", err)
		return
	}

	var AllAS []model.IssueAS
	//获取所有所有发布过的年检
	err1 := DB.Table("issue_as").Where("State = ?", 1).Find(&AllAS).Error
	if err1 != nil {
		response.Response(c, http.StatusBadRequest, 400, nil, "发生错误2，请重试")
		log.Println("Error querying user:", err1)
		return
	}

	var AllCE []model.IssueCE

	//获取所有所有发布过的评优
	err11 := DB.Table("issue_ce").Where("State = ?", 1).Find(&AllCE).Error
	if err11 != nil {
		response.Response(c, http.StatusBadRequest, 400, nil, "发生错误2，请重试")
		log.Println("Error querying user:", err11)
		return
	}

	//获取校团委发布的年检表文件
	fileMap := make(map[int]model.AllFile)
	for _, as := range AllAS {
		var file model.AllFile
		err4 := DB.Table("all_file").Where("id=?", as.ATID).First(&file).Error
		if err4 != nil {
			response.Response(c, http.StatusBadRequest, 400, nil, "查询年检文件错误，请重试")
			return
		}
		fileMap[as.CPID] = file
	}

	//获取评优文件
	fileMap1 := make(map[int]model.AllFile)
	for _, ce := range AllCE {
		var file model.AllFile
		err41 := DB.Table("all_file").Where("id=?", ce.ATID).First(&file).Error
		if err41 != nil {
			response.Response(c, http.StatusBadRequest, 400, nil, "查询评优文件错误，请重试")
			return
		}
		fileMap1[ce.CEID] = file
	}

	var showAS []model.ShowAS
	var showCE []model.ShowCE
	for _, eachclub := range clubInfo {
		//查询社团信息
		var club model.Club
		err3 := DB.Table("clubs").Where("id = ?", eachclub.Cid).First(&club).Error
		if err3 != nil {
			response.Response(c, http.StatusBadRequest, 400, nil, "查询社团信息失败！")
			return
		}

		for _, eachAS := range AllAS {
			var record model.ShowAS
			//查询年检申请表
			var applyAS model.Apply_AS
			err2 := DB.Table("apply_as").Where("cid = ? AND CPid = ? AND state != ?", eachclub.Cid, eachAS.CPID, 1).First(&applyAS).Error
			fmt.Println("cid:", eachclub.Cid)
			fmt.Println("eachAS.CPID:", eachAS.CPID)
			fmt.Println("applyAS:", applyAS)
			//fmt.Print("applyAS", applyAS.ID)
			//fmt.Println("state:", applyAS.State)
			if err2 != nil {
				//未找到记录 即没有提交
				if err2 == gorm.ErrRecordNotFound {
					record = model.ShowAS{
						Cid:       eachclub.Cid,
						CName:     club.Cname,
						CPID:      eachAS.CPID,
						CPName:    eachAS.CPName, //年检主题
						FileID:    fileMap[eachAS.CPID].ID,
						FileUrl:   mssql.VarChar(fileMap[eachAS.CPID].FileUrl),
						FileName:  mssql.VarChar(fileMap[eachAS.CPID].Filename),
						Year:      eachAS.Year,
						IssueTime: eachAS.PTime,
						State:     -1,
					}
					showAS = append(showAS, record)
				} else {
					//查询年检申请表失败
					response.Response(c, http.StatusBadRequest, 400, nil, "查询年检申请表失败！")
					return
				}
			} else {

				fmt.Println("applyCE.State", applyAS.State)
				//对于年检申请表中的记录
				var file model.AllFile
				err5 := DB.Table("all_file").Where("id=?", applyAS.File).Find(&file).Error
				fmt.Print("cid:", eachclub.Cid)
				fmt.Print("cpid:", eachclub.Cid)
				fmt.Print("fileid:", applyAS.File)
				if err5 != nil {
					response.Response(c, http.StatusBadRequest, 400, nil, "查询年检申请表文件信息失败！")
					return
				}
				record = model.ShowAS{
					Cid:       eachclub.Cid,
					CName:     club.Cname,
					CPID:      eachAS.CPID,
					CPName:    eachAS.CPName, //年检主题
					FileID:    file.ID,
					FileName:  mssql.VarChar(file.Filename),
					FileUrl:   mssql.VarChar(file.FileUrl),
					Year:      eachAS.Year,
					IssueTime: eachAS.PTime,
					State:     applyAS.State,
				}
				showAS = append(showAS, record)
			}

		}

		for _, eachCE := range AllCE {
			var record model.ShowCE
			//查询申请表
			var applyCE model.Apply_CE
			err2 := DB.Table("apply_ce").Where("cid = ? AND CEid = ? AND state != ?", eachclub.Cid, eachCE.CEID, 1).First(&applyCE).Error

			if err2 != nil {
				//未找到记录 即没有提交
				if err2 == gorm.ErrRecordNotFound {
					record = model.ShowCE{
						Cid:       eachclub.Cid,
						CName:     club.Cname,
						CEID:      eachCE.CEID,
						CEName:    eachCE.CEName,
						FileID:    fileMap1[eachCE.CEID].ID,
						FileUrl:   mssql.VarChar(fileMap1[eachCE.CEID].FileUrl),
						FileName:  mssql.VarChar(fileMap1[eachCE.CEID].Filename),
						Year:      eachCE.Year,
						IssueTime: eachCE.PTime,
						State:     -1,
					}
					showCE = append(showCE, record)
					//continue
				} else {
					//查询年检申请表失败
					response.Response(c, http.StatusBadRequest, 400, nil, "查询评优申请表失败！")
					return
				}
			} else {
				//对于年检申请表中的记录
				var file model.AllFile
				err5 := DB.Table("all_file").Where("id=?", applyCE.File).Find(&file).Error
				if err5 != nil {
					response.Response(c, http.StatusBadRequest, 400, nil, "查询评优申请表文件信息失败！")
					return
				} else {

					fmt.Println("applyCE.State", applyCE.State)
					record = model.ShowCE{
						Cid:       eachclub.Cid,
						CName:     club.Cname,
						CEID:      eachCE.CEID,
						CEName:    eachCE.CEName,
						FileID:    file.ID,
						FileName:  mssql.VarChar(file.Filename),
						FileUrl:   mssql.VarChar(file.FileUrl),
						Year:      eachCE.Year,
						IssueTime: eachCE.PTime,
						State:     applyCE.State,
					}
					showCE = append(showCE, record)
				}
			}

		}
	}

	response.Success(c, gin.H{"allASApply": showAS, "allCEApply": showCE}, "成功获取管理员端年检审批信息！")
}

// 管理员端上传年检表
func uploadASFile(ctx *gin.Context) {
	//上传文件函数，获取数据库中的file主键
	fileID := UploadFileToCOS(ctx)

	if fileID == -1 {
		response.Response(ctx, http.StatusBadRequest, 400, nil, "文件上传发生错误，请重试")
		return
	} else {
		response.Success(ctx, gin.H{"fileID": fileID}, "上传年检文件成功！")
		return
	}
}

// 管理员端上传评优表
func UploadCEFile(ctx *gin.Context) {
	//上传文件函数，获取数据库中的file主键

	fileID := UploadFileToCOS(ctx)
	if fileID == -1 {
		response.Response(ctx, http.StatusBadRequest, 400, nil, "文件上传发生错误，请重试")
		return
	} else {

		uid := ctx.PostForm("uid") //申请创建社团的用户id
		userid, _ := strconv.Atoi(uid)

		Cid := ctx.PostForm("cid") //申请创建社团的用户id
		cid, _ := strconv.Atoi(Cid)

		ceid := ctx.PostForm("CEid")
		CEid, _ := strconv.Atoi(ceid)

		Time := ctx.PostForm("applyTime") //申请创建社团时间

		applyTime, _ := time.Parse("2006-01-02 15:04:05", Time) //转换时间的格式

		applyAS := model.Apply_CE{
			CID:       cid,
			UID:       userid,
			ApplyTime: applyTime,
			State:     0,
			File:      fileID,
			CEID:      CEid,
		}

		// 得到所连接的数据库
		DB := common.GetDB()
		//插入apply_as数据表
		if Err := DB.Table("apply_ce").Create(&applyAS).Error; Err != nil {
			fmt.Println("执行 SQL 语句时发生错误:", Err)
			response.Response(ctx, http.StatusBadRequest, 400, nil, "发生错误，请重试")
			return
		} else {
			response.Success(ctx, gin.H{"fileID": fileID}, "上传评优文件成功！")
			return
		}

	}
}

// 管理员端上传年检表
func UploadASFile(ctx *gin.Context) {
	//上传文件函数，获取数据库中的file主键

	fileID := UploadFileToCOS(ctx)
	if fileID == -1 {
		response.Response(ctx, http.StatusBadRequest, 400, nil, "文件上传发生错误，请重试")
		return
	} else {

		uid := ctx.PostForm("uid") //申请创建社团的用户id
		userid, _ := strconv.Atoi(uid)

		Cid := ctx.PostForm("cid") //申请创建社团的用户id
		cid, _ := strconv.Atoi(Cid)

		CPID := ctx.PostForm("cpid")
		cpid, _ := strconv.Atoi(CPID)

		Time := ctx.PostForm("applyTime") //申请创建社团时间

		applyTime, _ := time.Parse("2006-01-02 15:04:05", Time) //转换时间的格式

		applyAS := model.Apply_AS{
			CID:       cid,
			UID:       userid,
			ApplyTime: applyTime,
			State:     0,
			File:      fileID,
			CPID:      cpid,
		}

		// 得到所连接的数据库
		DB := common.GetDB()
		//插入apply_as数据表
		if Err := DB.Table("apply_as").Create(&applyAS).Error; Err != nil {
			fmt.Println("执行 SQL 语句时发生错误:", Err)
			response.Response(ctx, http.StatusBadRequest, 400, nil, "发生错误，请重试")
			return
		} else {
			response.Success(ctx, gin.H{"fileID": fileID}, "上传年检文件成功！")
			return
		}

	}
}
